﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;
using CRMEntity;

namespace CRMBiz
{
    public static class DualTemplateHandler
    {
        public static List<Template> templatesListResult = null;
        public static bool collepse = false;
        public static DataTable GetDualTemplates(int DualTemplateId)
        {
            DataTable dt;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@DualTemplateId", DualTemplateId)
                };
                string query = " SELECT et.EmailTemplate,et.TemplateFormat as Emailformat,st.SMSTemplate,st.TemplateFormat as Smsformat,dt.CategoryId,dt.SmsTemplateID,dt.EmailTemplateID,dt.DualSubject from crm_dualtemplate dt " + 
                               " Left JOIN crm_emailtemplate et ON dt.EmailTemplateID=et.EmailId " + 
                              " Left JOIN crm_smstemplate st ON dt.SmsTemplateID=st.SMSTemplateID where dt.DualTemplateID=@DualTemplateId";
                dt = DBhelper.ExecuteDataset(query, para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        public static DataTable GetDualTemplates1()
        {
            DataTable dt;
            try
            {
                string query = "Select dt.* ,et.TemplateFormat as EmailTemplateFormat,et.emailsubject,et.EmailTemplate, st.TemplateFormat as SmsTemplateFormat,st.SMSTemplate FROM crm_dualtemplate dt inner join crm_emailtemplate et on dt.EmailTemplateID=et.EmailId inner join crm_smstemplate st on dt.SmsTemplateID=st.SMSTemplateID ";
                dt = DBhelper.ExecuteDataset(query).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// Get Treeview Nodes
        /// </summary>
        /// <returns></returns>

        public static List<CategoryNode> GetCategoryNodesList()
        {
            TemplateCategory templateCategory = null;
            List<TemplateCategory> templateCategoriesList = new List<TemplateCategory>();
            int currentCategoryId = 0;
            try
            {
                getTemplatesList();
                string sql = "SELECT CategoryId,CategoryName,ParentId FROM crm_dualtemplatecategory";
                DataTable dt = DBhelper.ExecuteDataset(sql).Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    templateCategory = new TemplateCategory();
                    templateCategory.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                    templateCategory.CategoryName = dr["CategoryName"].ToString().Trim();
                    templateCategory.ParentId = Convert.ToInt16(dr["ParentId"].ToString().Trim());
                    templateCategoriesList.Add(templateCategory);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            var root = from r in templateCategoriesList
                       where r.ParentId == 0
                       select r;

            currentCategoryId = root.First().CategoryId;
            List<CategoryNode> rootnodes = new List<CategoryNode>();
            CategoryNode rootnode = new CategoryNode();
            rootnode.id = currentCategoryId;
            rootnode.text = root.First().CategoryName;
            rootnode.children = GetChildrenNodes(templateCategoriesList, currentCategoryId, collepse);

            List<CategoryNode> rootTemplate = new List<CategoryNode>();
            rootTemplate = GetBottomNodeContents(currentCategoryId);
            //Template childNode = null;
            foreach (CategoryNode category in rootTemplate)
            {
                rootnode.children.Add(category);
            }
            rootnodes.Add(rootnode);
            return rootnodes;
        }

        /// <summary>
        /// Get templates list
        /// </summary>
        /// <param name="templateTableName"></param>
        private static void getTemplatesList()
        {
            Template template = null;
            templatesListResult = new List<Template>();
            string sbSql = null;
            try
            {
                sbSql = "Select DualTemplateID,DualTemplate,CategoryId From crm_dualtemplate";
                DataTable dualTemplateTable = DBhelper.ExecuteDataset(sbSql).Tables[0];
                foreach (DataRow dr in dualTemplateTable.Rows)
                {
                    template = new Template();
                    template.TemplateId = Convert.ToInt16(dr["DualTemplateID"].ToString().Trim());
                    template.TemplateTitle = dr["DualTemplate"].ToString().Trim();
                    template.CategoryId = Convert.ToInt16(dr["CategoryId"].ToString().Trim());
                    templatesListResult.Add(template);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get children Nodes
        /// </summary>
        /// <param name="categoriesList"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        private static List<CategoryNode> GetChildrenNodes(List<TemplateCategory> categoriesList, int parentId, bool isCollepse)
        {
            List<CategoryNode> childrenNodesList = new List<CategoryNode>();
            CategoryNode childNode = null;
            int currentCategoryId = 0;

            var childrenCategories = from r in categoriesList
                                     where r.ParentId == parentId
                                     select r;

            foreach (TemplateCategory category in childrenCategories)
            {
                childNode = new CategoryNode();
                currentCategoryId = category.CategoryId;
                childNode.id = currentCategoryId;
                childNode.text = category.CategoryName;

                if (isCollepse == true)
                    childNode.state = "closed";
                else
                    childNode.state = "open";

                childNode.children = GetChildrenNodes(categoriesList, category.CategoryId, true);

                childrenNodesList.Add(childNode);
            }
            foreach (CategoryNode category in childrenNodesList)
            {
                List<CategoryNode> rootTemplate = new List<CategoryNode>();
                rootTemplate = GetBottomNodeContents(category.id);

                foreach (CategoryNode template in rootTemplate)
                {
                    category.children.Add(template);
                }
                if (category.children.Count == 0)
                {
                    category.id = -1;
                    category.state = "open";
                    category.iconCls = "icon-folder";
                }
            }

            return childrenNodesList;
        }
        /// <summary>
        /// Get contents of bottom Node
        /// </summary>
        /// <param name="currentCategoryId"></param>
        /// <returns></returns>
        private static List<CategoryNode> GetBottomNodeContents(int currentCategoryId)
        {
            List<CategoryNode> bottomNodesList = new List<CategoryNode>();

            CategoryNode node = null;

            var result = from e in templatesListResult
                         where e.CategoryId == currentCategoryId
                         select e;

            foreach (Template e in result)
            {
                node = new CategoryNode();
                node.id = e.TemplateId;
                node.text = e.TemplateTitle;
                bottomNodesList.Add(node);
            }

            return bottomNodesList;
        }

        public static bool saveDualTemplate(int DualTemplateID, string DualSubject, string DualTemplate, int SmsTemplateID, int EmailTemplateID, int CategoryID)
        {
            bool flag = false;
            try
            {
                string query = string.Empty;
                if (DualTemplateID <= 0)
                    query = "Insert into crm_dualtemplate (DualTemplate,DualSubject,SmsTemplateID,EmailTemplateID,CategoryId) values ( '" + DualTemplate + "','" + DualSubject + "'," + SmsTemplateID + "," + EmailTemplateID + " ," + CategoryID + ")";
                else
                    query = "Update crm_dualtemplate SET DualTemplate='" + DualTemplate + "',DualSubject='" + DualSubject + "',SmsTemplateID=" + SmsTemplateID + ",EmailTemplateID=" + EmailTemplateID + ", CategoryID=" + CategoryID + " where DualTemplateID=" + DualTemplateID;
                DBhelper.ExecuteNonQuery(query);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        public static bool deleteDualTemplate(int DualTemplateID)
        {
            bool flag = false;
            try
            {
                DBhelper.ExecuteNonQuery("Delete from crm_dualtemplate where DualTemplateID=" + DualTemplateID);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }
    }
}
